//
//  SQLChainHandleViewController.m
//  SQL演练
//
//  Created by Guo Rui on 2017/10/28.
//  Copyright © 2017年 GR Harry. All rights reserved.
//

#import "SQLChainHandleViewController.h"
#import "SQLHandler.h"
#import <FMDB.h>

@interface SQLChainHandleViewController ()

@end

@implementation SQLChainHandleViewController

- (void)viewDidLoad {
    [super viewDidLoad];
    
    [SQLHandler.sharedInstance databaseWithName:@"test"];
    
    
    // DDL
//    [self excuteCreateTableTest];
    
//    [self dropTableTest];
    //    [self excuteAddColumnTest];
//    [self renameTableTest];
    
    // DML
//    [self excuteInsertTest];
    //    [self excuteInsertStudentWithIcon];
    
    
    
    // DQL
    [self excuteSelectTest];
//    [self excuteSelectIcon];
//    [self excuteJoinSelectTest];
    
    
//    [self useSQLHandle];
}

- (void)useFMDB
{
    
    [SQLHandler.sharedInstance.dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
        NSTimeInterval start = CFAbsoluteTimeGetCurrent();
        NSMutableString *sql = [NSMutableString stringWithCapacity:0];
        for (int i = 0; i < 10000; i++) {
            NSString *name = [NSString stringWithFormat:@"xx_%zd", i];
            [sql appendFormat:@"INSERT INTO t_student ('name','class_id') VALUES ('%@', 3);", name];
        }
        
        [db beginTransaction];
        [db executeStatements:sql];
        [db commit];
        NSTimeInterval end = CFAbsoluteTimeGetCurrent();
        NSLog(@"cost - %f", end - start);
    }];
}

- (void)useSQLHandle
{
    [SQLHandler makeSafelyHandleSQL:^(SQLHandler *SQL) {
        NSTimeInterval start = CFAbsoluteTimeGetCurrent();
        for (int i = 0; i < 10000; i++) {
            NSString *name = [NSString stringWithFormat:@"xx_%zd", i];
            SQL.generate.insert_into(@"t_student", @[@"name", @"class_id"], @[name, @1]).end();
        }

        [SQL excute];
        NSTimeInterval end = CFAbsoluteTimeGetCurrent();
        NSLog(@"cost - %f %@", end - start, [NSThread currentThread]);
    }];
}

- (void)renameTableTest
{
    [SQLHandler makeSafelyHandleSQL:^(SQLHandler *SQL) {
        SQL.generate.renameTable(@"t_student", @"t_stu");
        if (SQL.excute.isSuccess) {
            NSLog(@"yes");
        }
        else {
            NSLog(@"no");
        }
    }];
}

- (void)dropTableTest
{
    [SQLHandler makeSafelyHandleSQL:^(SQLHandler *SQL) {
        SQL.generate.dropTable(@"t_student");
        
        SQLHandleResult *result = SQL.excute;
        if (result.isSuccess) {
            NSLog(@"yes");
        }
        else {
            NSLog(@"no");
        }
    }];
}

- (void)excuteSelectIcon
{
    [SQLHandler makeSafelyHandleSQL:^(SQLHandler *SQL) {
        SQL.generate.select(@[@"name", @"icon"]).from(@"s_stu").end();
        
        SQL.excute.enumerateSet(^(FMResultSet *set){
            NSData *data = [set dataForColumn:@"icon"];
            if (data) {
                NSString *name = [set stringForColumn:@"name"];
                UIImage *image = [UIImage imageWithData:data];
                NSLog(@"name :%@ -- %@", name, image);
            }
        });
    }];
}
//
- (void)excuteInsertStudentWithIcon
{
    UIImage *image = [UIImage imageNamed:@"1"];
    NSData *data = UIImagePNGRepresentation(image);

//    [SQLHandler.sharedInstance safeThreadHandle:^(SQLChain *make) {
//        make.insert(@"t_student").fields(@[@"name", @"class_id", @"icon"]).values(@[@"jack", @1, data]);
//    } result:^(SQLHandleResult *result) {
//        if (result.isSuccess) {
//            NSLog(@"yes");
//        }
//        else NSLog(@"no");
//    }];
    
//    [SQLHandler.sharedInstance.dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
//        NSString *sql = @"INSERT INTO t_student (name, class_id, icon) VALUES (?, ?, ?)";
//        BOOL success = [db executeUpdate:sql withArgumentsInArray:@[@"mm", @1, data]];
//        if (success) {
//            NSLog(@"yes");
//        }
//        else NSLog(@"no");
//    }];
    
//    UIImage *image2 = [UIImage imageNamed:@"2"];
//    NSData *data2 = UIImagePNGRepresentation(image2);
//    [SQLHandler.sharedInstance.dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
//        NSString *sql = @"UPDATE t_student SET icon = ? where name = 'mm'";
//        BOOL success = [db executeUpdate:sql withArgumentsInArray:@[data2]];
//        if (success) {
//            NSLog(@"yes");
//        }
//        else NSLog(@"no");
//    }];
    
//    [SQLHandler.sharedInstance.dbQueue inDatabase:^(FMDatabase * _Nonnull db) {
//            NSString *sql = @"SELECT name, icon FROM t_student where name = 'mm'";
//            FMResultSet *set = [db executeQuery:sql];
//        [db executeQuery:@"" withArgumentsInArray:@[]];
//        while ([set next]) {
//            NSData *data = [set dataForColumn:@"icon"];
//            UIImage *image = [UIImage imageWithData:data];
//            NSLog(@"%@", image);
//        }


    [SQLHandler makeSafelyHandleSQL:^(SQLHandler *SQL) {
        SQL.generate.insert(@"t_student").fields(@[@"name", @"class_id", @"icon"]).values(@[@"?", @"?", @"?"]).end();
        
        SQLHandleResult *result = SQL.excuteWithArgumentsInArray(@[@"cc", @1, data]);
        if (result.isSuccess) {
            NSLog(@"yes");
        }
        else {
            NSLog(@"no");
        }
    }];
}

- (void)excuteAddColumnTest
{
    [SQLHandler makeSafelyHandleSQL:^(SQLHandler *SQL) {
        SQL.generate.addcolumn(@"t_student", @"icon BLOB");
        SQLHandleResult *reslut = [SQL excute];
        if (reslut.isSuccess) {
            NSLog(@"yes");
        }
        else {
            NSLog(@"no");
        }
    }];
}
//
- (void)excuteJoinSelectTest
{

    [SQLHandler makeSafelyHandleSQL:^(SQLHandler *SQL) {
        
        SQL.generate.select(@[@"tc.id as class_id, tc.name as class_name", @"ts.id as student_id, ts.name as student_name"]).from(@"t_class as tc, t_student as ts").where(@"ts.class_id = tc.id AND tc.name = '15级1班'");
        
        SQL.excute.enumerateSet(^(FMResultSet *set){
            int class_id = [set intForColumn:@"class_id"];
            int student_id = [set intForColumn:@"student_id"];
            NSString *class_name = [set stringForColumn:@"class_name"];
            NSString *student_name = [set stringForColumn:@"student_name"];
            NSLog(@"%zd -- %@, %zd -- %@", class_id, class_name, student_id, student_name);
            
        });
    }];
}

- (void)excuteInsertTest
{
    
    [SQLHandler makeSafelyHandleSQL:^(SQLHandler *SQL) {
        SQL.generate.insert(@"t_student").fields(@[@"name", @"class_id"]).values(@[@"jack", @1]).end();
        SQL.generate.insert(@"t_student").fields(@[@"name", @"class_id"]).values(@[@"rose", @2]).end();
        SQL.generate.insert(@"t_student").fields(@[@"name", @"class_id"]).values(@[@"jim", @3]).end();
        SQL.generate.insert(@"t_student").fields(@[@"name", @"class_id"]).values(@[@"tik", @1]).end();
        SQL.generate.insert(@"t_student").fields(@[@"name", @"class_id"]).values(@[@"ban", @3]).end();
        SQL.generate.insert(@"t_student").fields(@[@"name", @"class_id"]).values(@[@"lusi", @2]).end();
        SQL.generate.insert(@"t_student").fields(@[@"name", @"class_id"]).values(@[@"lili", @2]).end();

        SQLHandleResult *result = SQL.excute;
        if (result.isSuccess) {
            NSLog(@"yes");
        }
        else {
            NSLog(@"no");
        }
    }];
    
//    [SQLHandler.sharedInstance makeTransactionHandleSQL:^(SQLHandler *SQL, BOOL *rollback) {
//        SQL.generate.insert(@"t_student").fields(@[@"name", @"class_id"]).values(@[@"jack", @1]).end();
//
//        SQLHandleResult *result = SQL.excute;
//        if (result.isSuccess) {
//            *rollback = YES;
//        }
//        else {
//            NSLog(@"no");
//        }
//    }];
}
//
- (void)excuteCreateTableTest
{
    
    [SQLHandler makeSafelyHandleSQL:^(SQLHandler *SQL) {
        SQL.generate.createTable(@"t_class").fieldsDefine(@"(id INTEGER PRIMARY KEY AUTOINCREMENT, name text NOT NULL)").end();
        SQL.generate.createTable(@"t_student").fieldsDefine(@"(id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT NOT NULL, class_id INTEGER, CONSTRAINT fk_student_id_class_id FOREIGN KEY (class_id) REFERENCES t_class(id))").end();
        
        if (SQL.excute.isSuccess) {
            NSLog(@"yes");
        }
        else {
            NSLog(@"no");
        }
    }];
}

- (void)excuteSelectTest
{
    [SQLHandler makeTransactionHandleSQL:^(SQLHandler *SQL, BOOL *rollback) {
        SQL.generate.selectAll().from(@"t_student");
        
        SQL.excute.enumerateSet(^(FMResultSet *set){
            int stu_id = [set intForColumn:@"id"];
            NSString *name = [set stringForColumn:@"name"];
            int cls_id = [set intForColumn:@"class_id"];
            SQLLog(@"stu_id :%zd, name :%@, cls_id :%zd", stu_id, name, cls_id);
        });
    }];
}


@end
